﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Common;
using Common.Views;

namespace DataAccess
{
    public class FaultsRepository : ConnectionClass
    {
        public FaultsRepository() : base() { }

        public IQueryable<Fault> GetAllFaults()
        {
            return Entity.Faults.AsQueryable();
        }

        public void AddFault(Fault f)
        {
            Entity.Faults.AddObject(f);
            Entity.SaveChanges();
        }

        public void AddFaultDetails(FaultDetail fd)
        {
            Entity.FaultDetails.AddObject(fd);
            Entity.SaveChanges();
        }

        public string GetFaultsForOrder(Guid orderID, int productID)
        {
            return (
                    from f in Entity.Faults
                    join fd in Entity.FaultDetails
                    on f.FaultID equals fd.FaultID
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID

                    where f.OrderID == orderID
                    && f.ProductID == productID
                    orderby fd.Date descending
                    select fs.Status
                ).FirstOrDefault();
        }

        public string GetFaultStatus(int faultStatusID)
        {
            return (
                    from fs in Entity.FaultStatus
                    where fs.StatusID == faultStatusID
                    select fs.Status
                ).SingleOrDefault();
        }

        public IQueryable<FaultCategory> GetFaultCategories()
        {
            return Entity.FaultCategories.AsQueryable();
        }

        public IQueryable<OrdersView> FilterTransactions(DateTime startDate, DateTime endDate, int productID, string username)
        {
            return (
                    from p in Entity.Products
                    join od in Entity.OrderDetails
                    on p.ProductID equals od.ProductID
                    join o in Entity.Orders
                    on od.OrderID equals o.OrderID
                    join f in Entity.Faults
                    on p.ProductID equals f.ProductID
                    join fd in Entity.FaultDetails
                    on f.FaultID equals fd.FaultID
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID

                    where p.ProductID == productID &&
                          o.Username == username &&
                          fd.Date >= startDate &&
                          fd.Date <= endDate

                    select new OrdersView()
                    {
                        OrderID = o.OrderID,
                        ProductID = p.ProductID,
                        ProductName = p.Name,
                        ImageLink = p.Image,
                        Price = p.Price,
                        Features = p.Features,
                        Quantity = od.ProductQty,
                        OrderDate = o.OrderDate,
                        WarrantyExpiryDate = od.WarrantyExpiryDate,
                        FaultStatus = fs.Status,
                        FaultDescription = fs.Description,
                        FaultDetails = fd.Details,
                        FaultDate = fd.Date
                    }).Distinct();
        }

        public IQueryable<FaultsView> SortEntriesByDate()
        {
            return (
                    from fd in Entity.FaultDetails
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID

                    select new FaultsView()
                    {
                        FaultID = fd.FaultID,
                        FaultDate = fd.Date,
                        FaultDetails = fd.Details,
                        FaultStatus = fs.Status
                    }).OrderByDescending(f => f.FaultDate);
        }

        public IQueryable<FaultsView> FilterByLogDate(DateTime start, DateTime end)
        {
            return (
                    from fd in Entity.FaultDetails
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID

                    where fd.Date >= start &&
                          fd.Date <= end

                    select new FaultsView()
                    {
                        FaultID = fd.FaultID,
                        FaultDate = fd.Date,
                        FaultDetails = fd.Details,
                        FaultStatus = fs.Status
                    }).Distinct();
        }

        public IQueryable<FaultsView> FilterByFaultID(Guid faultID)
        {
            return (
                    from fd in Entity.FaultDetails
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID

                    where fd.FaultID == faultID

                    select new FaultsView()
                    {
                        FaultID = fd.FaultID,
                        FaultDate = fd.Date,
                        FaultDetails = fd.Details,
                        FaultStatus = fs.Status
                    }).Distinct();
        }

        public IQueryable<FaultsView> FilterByClientID(string username)
        {
            return (
                    from fd in Entity.FaultDetails
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID
                    join f in Entity.Faults
                    on fd.FaultID equals f.FaultID
                    join o in Entity.Orders
                    on f.OrderID equals o.OrderID

                    where o.Username == username

                    select new FaultsView()
                    {
                        FaultID = fd.FaultID,
                        FaultDate = fd.Date,
                        FaultDetails = fd.Details,
                        FaultStatus = fs.Status
                    }).Distinct();
        }

        public IQueryable<FaultsView> FilterAll(Guid faultID, string username, DateTime startDate, DateTime endDate)
        {
            return (
                    from fd in Entity.FaultDetails
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID
                    join f in Entity.Faults
                    on fd.FaultID equals f.FaultID
                    join o in Entity.Orders
                    on f.OrderID equals o.OrderID

                    where fd.FaultID == faultID &&
                          o.Username == username &&
                          fd.Date >= startDate &&
                          fd.Date <= endDate

                    select new FaultsView()
                    {
                        FaultID = fd.FaultID,
                        FaultDate = fd.Date,
                        FaultDetails = fd.Details,
                        FaultStatus = fs.Status
                    }).Distinct();
        }

        public IQueryable<FaultsView> GetAllLogEntires()
        {
            return (
                    from o in Entity.Orders
                    join f in Entity.Faults
                    on o.OrderID equals f.OrderID
                    join fd in Entity.FaultDetails
                    on f.FaultID equals fd.FaultID
                    join fs in Entity.FaultStatus
                    on fd.FaultStatusID equals fs.StatusID
                    join p in Entity.Products
                    on f.ProductID equals p.ProductID

                    select new FaultsView()
                    {
                        FaultID = f.FaultID,
                        FaultDate = fd.Date,
                        FaultDetails = fd.Details,
                        FaultStatus = fs.Status,
                        FaultCategoryID = f.FaultCategoryID,
                        Username = o.Username,
                        OrderID = o.OrderID,
                        ProductID = p.ProductID,
                        ProductName = p.Name,
                        ImageLink = p.Image
                    }).AsQueryable();
        }

        public IQueryable<FaultStatu> GetAllFaultStatuses()
        {
            return Entity.FaultStatus.AsQueryable();
        }
    }
}
